CREATE TABLE Albums
(
	id                NUMBER(8,2) NOT NULL,
	title             NVARCHAR2(100),
	publication_year  DATE,
	songs_count       NUMBER(8,2),
	genre             NVARCHAR2(50),
	publisher_id      NUMBER(8,2)
);


CREATE TABLE Articles
(
	id            NUMBER(8,2) NOT NULL,
	title         NVARCHAR2(200),
	kind          NVARCHAR2(50),
	author_id     NUMBER(8,2),
	newspaper_id  NUMBER(8,2)
);


CREATE TABLE Authors
(
	id          NUMBER(8,2) NOT NULL,
	first_name  NVARCHAR2(100),
	last_name   NVARCHAR2(100),
	birth_date  DATE,
	country     NVARCHAR2(100)
);


CREATE TABLE Books
(
	id                NUMBER(8,2) NOT NULL,
	title             NVARCHAR2(100),
	publication_year  DATE,
	page_count        NUMBER(8,2),
	genre             NVARCHAR2(50),
	author_id         NUMBER(8,2),
	publisher_id      NUMBER(8,2)
);


CREATE TABLE Newspapers
(
	id            NUMBER(8,2) NOT NULL,
	title         NVARCHAR2(100),
	country       NVARCHAR2(100),
	kind          NVARCHAR2(50),
	publisher_id  NUMBER(8,2)
);


CREATE TABLE Publishers
(
	id              NUMBER(8,2) NOT NULL,
	name            NVARCHAR2(100),
	country         NVARCHAR2(100),
	establish_date  DATE
);


CREATE TABLE Songs
(
	id         NUMBER(8,2) NOT NULL,
	title      NVARCHAR2(100),
	duration   INTERVAL DAY TO SECOND,
	author_id  NUMBER(8,2),
	album_id   NUMBER(8,2)
);



ALTER TABLE Albums ADD CONSTRAINT PK_Albums 
	PRIMARY KEY (id);

ALTER TABLE Articles ADD CONSTRAINT PK_Articles 
	PRIMARY KEY (id);

ALTER TABLE Authors ADD CONSTRAINT PK_Author 
	PRIMARY KEY (id);

ALTER TABLE Books ADD CONSTRAINT PK_Books 
	PRIMARY KEY (id);

ALTER TABLE Newspapers ADD CONSTRAINT PK_Newspapers 
	PRIMARY KEY (id);

ALTER TABLE Publishers ADD CONSTRAINT PK_Publishers 
	PRIMARY KEY (id);

ALTER TABLE Songs ADD CONSTRAINT PK_Songs 
	PRIMARY KEY (id);



ALTER TABLE Albums ADD CONSTRAINT FK_Albums_Publishers 
	FOREIGN KEY (publisher_id) REFERENCES Publishers (id);

ALTER TABLE Articles ADD CONSTRAINT FK_Articles_Authors 
	FOREIGN KEY (author_id) REFERENCES Authors (id);

ALTER TABLE Articles ADD CONSTRAINT FK_Articles_Newspapers 
	FOREIGN KEY (newspaper_id) REFERENCES Newspapers (id);

ALTER TABLE Books ADD CONSTRAINT FK_Books_Authors 
	FOREIGN KEY (author_id) REFERENCES Authors (id);

ALTER TABLE Books ADD CONSTRAINT FK_Books_Publishers 
	FOREIGN KEY (publisher_id) REFERENCES Publishers (id);

ALTER TABLE Newspapers ADD CONSTRAINT FK_Newspapers_Publishers 
	FOREIGN KEY (publisher_id) REFERENCES Publishers (id);

ALTER TABLE Songs ADD CONSTRAINT FK_Songs_Albums 
	FOREIGN KEY (album_id) REFERENCES Albums (id);

ALTER TABLE Songs ADD CONSTRAINT FK_Songs_Authors 
	FOREIGN KEY (author_id) REFERENCES Authors (id);






CREATE SEQUENCE Albums_id_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

CREATE OR REPLACE TRIGGER SET_Albums_id
BEFORE INSERT
ON Albums
FOR EACH ROW
BEGIN
  SELECT Albums_id_SEQ.NEXTVAL
  INTO :NEW.id
  FROM DUAL;
END;
/

CREATE SEQUENCE Articles_id_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

CREATE OR REPLACE TRIGGER SET_Articles_id
BEFORE INSERT
ON Articles
FOR EACH ROW
BEGIN
  SELECT Articles_id_SEQ.NEXTVAL
  INTO :NEW.id
  FROM DUAL;
END;
/

CREATE SEQUENCE Authors_id_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

CREATE OR REPLACE TRIGGER SET_Authors_id
BEFORE INSERT
ON Authors
FOR EACH ROW
BEGIN
  SELECT Authors_id_SEQ.NEXTVAL
  INTO :NEW.id
  FROM DUAL;
END;
/

CREATE SEQUENCE Books_id_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

CREATE OR REPLACE TRIGGER SET_Books_id
BEFORE INSERT
ON Books
FOR EACH ROW
BEGIN
  SELECT Books_id_SEQ.NEXTVAL
  INTO :NEW.id
  FROM DUAL;
END;
/

CREATE SEQUENCE Newspapers_id_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

CREATE OR REPLACE TRIGGER SET_Newspapers_id
BEFORE INSERT
ON Newspapers
FOR EACH ROW
BEGIN
  SELECT Newspapers_id_SEQ.NEXTVAL
  INTO :NEW.id
  FROM DUAL;
END;
/

CREATE SEQUENCE Publishers_id_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

CREATE OR REPLACE TRIGGER SET_Publishers_id
BEFORE INSERT
ON Publishers
FOR EACH ROW
BEGIN
  SELECT Publishers_id_SEQ.NEXTVAL
  INTO :NEW.id
  FROM DUAL;
END;
/

CREATE SEQUENCE Songs_id_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;

CREATE OR REPLACE TRIGGER SET_Songs_id
BEFORE INSERT
ON Songs
FOR EACH ROW
BEGIN
  SELECT Songs_id_SEQ.NEXTVAL
  INTO :NEW.id
  FROM DUAL;
END;
/
